package com.link2room.rodimus.facade.impl;

import com.link2room.common.dto.param.base.BaseL2rParamDto;
import com.link2room.common.dto.result.base.HotelBaseInfoDto;
import com.link2room.common.jpa.criteria.CriteriaUtil;
import com.link2room.common.jpa.result.QueryResult;
import com.link2room.common.service.ICommonService;
import com.link2room.common.util.ClassConverUtil;
import com.link2room.common.util.DateUtil;
import com.link2room.common.util.StringUtil;
import com.link2room.ironhide.dto.param.inside.HotelBaseInfoInsideParamDto;
import com.link2room.rodimus.constant.SalesmanPaymentConstant;
import com.link2room.rodimus.dto.param.manager.FansReportManagerParamDto;
import com.link2room.rodimus.dto.param.manager.MallOrderManagerParamDto;
import com.link2room.rodimus.dto.param.manager.PaymentReportManagerParamDto;
import com.link2room.rodimus.dto.result.base.SalesmanBaseDto;
import com.link2room.rodimus.entity.*;
import com.link2room.rodimus.facade.IReportFacadeService;
import com.link2room.rodimus.feign.ironhide.HotelBaseInfoFeignService;
import com.link2room.rodimus.mapper.*;
import com.link2room.rodimus.service.IReportService;
import org.jxls.area.Area;
import org.jxls.builder.AreaBuilder;
import org.jxls.builder.xml.XmlAreaBuilder;
import org.jxls.common.CellRef;
import org.jxls.common.Context;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.TransformerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by VegetaKo on 2016/12/19.
 */
@Service("reportFacadeService")
public class ReportFacadeServiceImpl implements IReportFacadeService {
    @Resource
    private IReportService reportService;

    @Resource
    private ICommonService commonService;

    @Resource
    private HotelBaseInfoFeignService hotelBaseInfoFeignService;

    private static final String DYNAMIC_COLUMNS_DEMO_XML_CONFIG = "/usr/local/ARK/outExcel/templet/dynamic_columns_demo.xml";
    private static final String TEMPLATE = "/usr/local/ARK/outExcel/dynamic_columns_demo.xls";
    private static final String OUTPUT = "/usr/local/ARK/outExcel/dynamic_columns_output.xls";

    private Date start;
    private Date end ;
    @Override
    public String getFansReportBySalesman(FansReportManagerParamDto fansReportManagerParamDto) throws Exception {

        SalesmanBase salesmanBase = new SalesmanBase();
        salesmanBase.setLhotelGroupCode(fansReportManagerParamDto.getLhotelGroupCode());
        HashMap<String, String> orderMap = new HashMap<String, String>();
        orderMap.put("lhotelCode", CriteriaUtil.ASC);
        orderMap.put("salesmanNo", CriteriaUtil.ASC);
        QueryResult<SalesmanBase> salesmanBaseQueryResult = commonService.findAllEQ(SalesmanBase.class, salesmanBase, 1, Integer.MAX_VALUE, orderMap, null);
        //QueryResult<SalesmanBase> salesmanBaseQueryResult = commonService.findAllEQ(SalesmanBase.class,salesmanBase);
        List<SalesmanBaseDto> salesmanBaseDtoQueryResult = ClassConverUtil.copyPropertiesListToDtoList(salesmanBaseQueryResult.getResultlist(), SalesmanBaseDto.class, true);
        //Date now = new Date();

        makeStartEnd(fansReportManagerParamDto.getStartDate() );
        fansReportManagerParamDto.setStartDate(start);
        fansReportManagerParamDto.setEndDate(end);
        QueryResult<FansReprotMapper> fansReprotMapperQueryResult = reportService.getFansReportBySalesman(fansReportManagerParamDto);


        List<Date> dates = new ArrayList<Date>();
        while (DateUtil.getDiffDays2(end, start) >= 0) {
            dates.add(end);
            end = DateUtil.addDays(end, -1);
        }

        List<String> headers = new ArrayList<String>(Arrays.asList("酒店名称", "姓名", "累计"));
        for (Date d : dates) {
            headers.add(DateUtil.format(d, "M月d日"));
        }

        List<List<Object>> rows = new ArrayList<>();
        for (SalesmanBaseDto sbd : salesmanBaseDtoQueryResult) {
            List<Object> row = new ArrayList<>();
            if (StringUtil.isBlank(sbd.getLhotelName())) {
                row.add("青藤酒店集团");
            } else {
                row.add(sbd.getLhotelName());
            }
            row.add(sbd.getSalesmanName());
            bizDate:
            for (Date d : dates) {
                Iterator<FansReprotMapper> fansReprotMapperIterator = fansReprotMapperQueryResult.getResultlist().iterator();
                while (fansReprotMapperIterator.hasNext()) {
                    FansReprotMapper frm = (FansReprotMapper) fansReprotMapperIterator.next();
                    if (frm.getSalesmanName().equals(sbd.getSalesmanName()) && frm.getBizDate().equals(DateUtil.format(d, DateUtil.webFormat))) {
                        row.add(frm.getCount());
                        fansReprotMapperQueryResult.getResultlist().remove(frm);
                        continue bizDate;
                    }

                }
                row.add(0);
            }
            Integer sum = 0;
            for (int ii = 2; ii < row.size(); ii++) {
                sum = sum + Integer.parseInt(row.get(ii).toString());
            }
            row.add(2, sum);
            rows.add(row);
        }

        makeTempExcel(headers, rows);

        return OUTPUT;
    }

    @Override
    public String getFansReportByHotel(FansReportManagerParamDto fansReportManagerParamDto) throws Exception {

        makeStartEnd(fansReportManagerParamDto.getStartDate() );

        fansReportManagerParamDto.setStartDate(start);
        fansReportManagerParamDto.setEndDate(end);
        QueryResult<FansReprotMapper> fansReprotMapperQueryResult = reportService.getFansReportByHotel(fansReportManagerParamDto);


        List<Date> dates = new ArrayList<Date>();
        while (DateUtil.getDiffDays2(end, start) >= 0) {
            dates.add(end);
            end = DateUtil.addDays(end, -1);
        }

        List<String> headers = new ArrayList<String>(Arrays.asList("酒店名称", "累计"));
        for (Date d : dates) {
            headers.add(DateUtil.format(d, "M月d日"));
        }

        List<List<Object>> rows = new ArrayList<>();

        //List<Object> row = new ArrayList<>();

        HotelBaseInfoInsideParamDto hotelBaseInfoInsideParamDto = new HotelBaseInfoInsideParamDto();
        hotelBaseInfoInsideParamDto.setLhotelGroupCode(fansReportManagerParamDto.getLhotelGroupCode() );
        QueryResult<HotelBaseInfoDto> hotelBaseInfoResult = hotelBaseInfoFeignService.hotelBaseInfoList(hotelBaseInfoInsideParamDto.paramToMap()).getQueryResult();
        List<HotelBaseInfoDto> hotelBaseInfoDtos = hotelBaseInfoResult.getResultlist();
        HotelBaseInfoDto hotelBaseInfoDto = new HotelBaseInfoDto();
        hotelBaseInfoDto.setLhotelCode("QINGTENG");
        hotelBaseInfoDto.setName("青藤集团");
        hotelBaseInfoDtos.add(0,hotelBaseInfoDto);
        for (HotelBaseInfoDto  hbid : hotelBaseInfoDtos) {
            List<Object> row = new ArrayList<>();
            row.add(hbid.getName());
            bizDate:
            for (Date d : dates) {
                Iterator<FansReprotMapper> fansReprotMapperIterator = fansReprotMapperQueryResult.getResultlist().iterator();
                while (fansReprotMapperIterator.hasNext()) {
                    FansReprotMapper frm = (FansReprotMapper) fansReprotMapperIterator.next();

                    if (frm.getLhotelCode().equals(hbid.getLhotelCode()) && frm.getBizDate().equals(DateUtil.format(d, DateUtil.webFormat))) {
                        row.add(frm.getCount());
                        fansReprotMapperQueryResult.getResultlist().remove(frm);
                        continue bizDate;
                    }

                }
                row.add(0);
            }
            Integer sum = 0;
            for (int ii = 1; ii < row.size(); ii++) {
                sum = sum + Integer.parseInt(row.get(ii).toString());
            }
            row.add(1, sum);
            rows.add(row);
        }
        makeTempExcel(headers, rows);

        return OUTPUT;
    }

    @Override
    public String getPaymentReportBySalesman(PaymentReportManagerParamDto paymentReportManagerParamDto) throws Exception {

        SalesmanBase salesmanBase = new SalesmanBase();
        salesmanBase.setLhotelGroupCode(paymentReportManagerParamDto.getLhotelGroupCode());
        HashMap<String, String> orderMap = new HashMap<String, String>();
        orderMap.put("lhotelCode", CriteriaUtil.ASC);
        orderMap.put("salesmanNo", CriteriaUtil.ASC);
        QueryResult<SalesmanBase> salesmanBaseQueryResult = commonService.findAllEQ(SalesmanBase.class, salesmanBase, 1, Integer.MAX_VALUE, orderMap, null);
        //QueryResult<SalesmanBase> salesmanBaseQueryResult = commonService.findAllEQ(SalesmanBase.class,salesmanBase);
        List<SalesmanBaseDto> salesmanBaseDtoQueryResult = ClassConverUtil.copyPropertiesListToDtoList(salesmanBaseQueryResult.getResultlist(), SalesmanBaseDto.class, true);
        //Date now = new Date();

        makeStartEnd(paymentReportManagerParamDto.getStartDate() );
        paymentReportManagerParamDto.setStartDate(start);
        paymentReportManagerParamDto.setEndDate(end);
        QueryResult<PaymentReportMapper> paymentReportMapperQueryResult = reportService.getPaymentReportBySalesman(paymentReportManagerParamDto);


        List<Date> dates = new ArrayList<Date>();
        while (DateUtil.getDiffDays2(end, start) >= 0) {
            dates.add(end);
            end = DateUtil.addDays(end, -1);
        }

        List<String> headers = new ArrayList<String>(Arrays.asList("酒店名称", "姓名", "月累计","会员累计","订单累计"));
        for (Date d : dates) {
            headers.add(DateUtil.format(d, "M月d日 合计"));
            headers.add(DateUtil.format(d, "M月d日 会员" ));
            headers.add(DateUtil.format(d, "M月d日 订单"));
        }

        List<List<Object>> rows = new ArrayList<>();
        for (SalesmanBaseDto sbd : salesmanBaseDtoQueryResult) {
            BigDecimal sum = BigDecimal.ZERO;
            BigDecimal sumMember = BigDecimal.ZERO;
            BigDecimal sumReservation = BigDecimal.ZERO;
            List<Object> row = new ArrayList<>();
            if (StringUtil.isBlank(sbd.getLhotelName())) {
                row.add("青藤酒店集团");
            } else {
                row.add(sbd.getLhotelName());
            }
            row.add(sbd.getSalesmanName());
            bizDate:
            for (Date d : dates) {
                Iterator<PaymentReportMapper> fansReprotMapperIterator = paymentReportMapperQueryResult.getResultlist().iterator();
                while (fansReprotMapperIterator.hasNext()) {
                    PaymentReportMapper prm = (PaymentReportMapper) fansReprotMapperIterator.next();
                    if (prm.getSalesmanName().equals(sbd.getSalesmanName()) && prm.getBizDate().equals(DateUtil.format(d, DateUtil.webFormat))) {
                        if( null!=prm.getTotal()){
                            row.add(prm.getTotal());
                            sum =sum.add( prm.getTotal());
                        }
                        else{
                            row.add(0);
                        }
                        if(null!=prm.getMember()){
                            row.add(prm.getMember());
                            sumMember = sumMember.add(prm.getMember());
                        }
                        else{
                            row.add(0);
                        }
                        if(null!=prm.getReservation()){
                            row.add(prm.getReservation());
                            sumReservation = sumReservation.add(prm.getReservation());
                        }
                        else{
                            row.add(0);
                        }
                        paymentReportMapperQueryResult.getResultlist().remove(prm);
                        continue bizDate;
                    }
                }
                row.add(0);
                row.add(0);
                row.add(0);
            }
            row.add(2, sum);
            row.add(3, sumMember);
            row.add(4, sumReservation);
            rows.add(row);
        }

        makeTempExcel(headers, rows);

        return OUTPUT;
    }

    @Override
    public String getPaymentReportByHotel(PaymentReportManagerParamDto paymentReportManagerParamDto) throws Exception {

        makeStartEnd(paymentReportManagerParamDto.getStartDate() );

        paymentReportManagerParamDto.setStartDate(start);
        paymentReportManagerParamDto.setEndDate(end);
        QueryResult<PaymentReportMapper> paymentReportMapperQueryResult = reportService.getPaymentReportByHotel(paymentReportManagerParamDto);


        List<Date> dates = new ArrayList<Date>();
        while (DateUtil.getDiffDays2(end, start) >= 0) {
            dates.add(end);
            end = DateUtil.addDays(end, -1);
        }

        List<String> headers = new ArrayList<String>(Arrays.asList("酒店名称", "月累计","会员累计","订单累计"));
        for (Date d : dates) {
            headers.add(DateUtil.format(d, "M月d日 合计"));
            headers.add(DateUtil.format(d, "M月d日 会员" ));
            headers.add(DateUtil.format(d, "M月d日 订单"));
        }

        List<List<Object>> rows = new ArrayList<>();

        //List<Object> row = new ArrayList<>();

        HotelBaseInfoInsideParamDto hotelBaseInfoInsideParamDto = new HotelBaseInfoInsideParamDto();
        hotelBaseInfoInsideParamDto.setLhotelGroupCode(paymentReportManagerParamDto.getLhotelGroupCode() );
        QueryResult<HotelBaseInfoDto> hotelBaseInfoResult = hotelBaseInfoFeignService.hotelBaseInfoList(hotelBaseInfoInsideParamDto.paramToMap()).getQueryResult();
        List<HotelBaseInfoDto> hotelBaseInfoDtos = hotelBaseInfoResult.getResultlist();HotelBaseInfoDto hotelBaseInfoDto = new HotelBaseInfoDto();
        hotelBaseInfoDto.setLhotelCode("QINGTENG");
        hotelBaseInfoDto.setName("青藤集团");
        hotelBaseInfoDtos.add(0,hotelBaseInfoDto);
        for (HotelBaseInfoDto  hbid : hotelBaseInfoDtos) {
            BigDecimal sum = BigDecimal.ZERO;
            BigDecimal sumMember = BigDecimal.ZERO;
            BigDecimal sumReservation = BigDecimal.ZERO;
            List<Object> row = new ArrayList<>();
            row.add(hbid.getName());
            bizDate:
            for (Date d : dates) {
                Iterator<PaymentReportMapper> paymentReportMapperIterator = paymentReportMapperQueryResult.getResultlist().iterator();
                while (paymentReportMapperIterator.hasNext()) {
                    PaymentReportMapper prm = (PaymentReportMapper) paymentReportMapperIterator.next();

                    if (prm.getLhotelCode().equals(hbid.getLhotelCode()) && prm.getBizDate().equals(DateUtil.format(d, DateUtil.webFormat))) {
                        if( null!=prm.getTotal()){
                            row.add(prm.getTotal());
                            sum =sum.add( prm.getTotal());
                        }
                        else{
                            row.add(0);
                        }
                        if(null!=prm.getMember()){
                            row.add(prm.getMember());
                            sumMember = sumMember.add(prm.getMember());
                        }
                        else{
                            row.add(0);
                        }
                        if(null!=prm.getReservation()){
                            row.add(prm.getReservation());
                            sumReservation = sumReservation.add(prm.getReservation());
                        }
                        else{
                            row.add(0);
                        }
                        paymentReportMapperQueryResult.getResultlist().remove(prm);
                        continue bizDate;
                    }

                }
                row.add(0);
                row.add(0);
                row.add(0);
            }
            row.add(1, sum);
            row.add(2, sumMember);
            row.add(3, sumReservation);
            rows.add(row);
        }
        makeTempExcel(headers, rows);
        return OUTPUT;
    }

    @Override
    public String getPaymentCheck(PaymentReportManagerParamDto paymentReportManagerParamDto) throws Exception {
        SalesmanPayment salesmanPayment = new SalesmanPayment();
        salesmanPayment.setLhotelGroupCode(paymentReportManagerParamDto.getLhotelGroupCode() );
        salesmanPayment.setAuditedDate(paymentReportManagerParamDto.getStartDate());
        salesmanPayment.setPaySta(SalesmanPaymentConstant.PaySta.paid);
        QueryResult<SalesmanPayment>  salesmanPaymentQueryResult = commonService.findAllEQ( SalesmanPayment.class,salesmanPayment);

        List<String> headers = new ArrayList<String>(Arrays.asList("支付流水号","分销员姓名", "支付金额"));
        List<List<Object>> rows = new ArrayList<>();
        if( null!= salesmanPaymentQueryResult && salesmanPaymentQueryResult.getResultlist().size()>0)
        for(SalesmanPayment sp :salesmanPaymentQueryResult.getResultlist()  ){
            List<Object> row = new ArrayList<>();
            row.add( sp.getPaymentNo());
            row.add( sp.getSalesmanName());
            row.add( sp.getTotal());
            rows.add(row);
        }

        makeTempExcel(headers, rows);
        return OUTPUT;

    }

    @Override
    public String getMallOrderByExpressSta(MallOrderManagerParamDto mallOrderManagerParamDto) throws Exception {

        MallOrderBase mallOrderBaseFinder =new MallOrderBase();
        mallOrderBaseFinder.setLhotelGroupCode(mallOrderManagerParamDto.getLhotelGroupCode() );
        mallOrderBaseFinder.setSta(mallOrderManagerParamDto.getStas());
        QueryResult<MallOrderBase> mallOrderBaseQueryResult = commonService.findAllEQ( MallOrderBase.class,mallOrderBaseFinder );

        List<MallOrderPriceMapper>  mallOrderPriceMappers = new ArrayList<>();
        if( null!=mallOrderBaseQueryResult && mallOrderBaseQueryResult.getResultlist().size()>0 ){
            for ( MallOrderBase mob:mallOrderBaseQueryResult.getResultlist()) {
                MallOrderPrice mallOrderPriceFinder= new MallOrderPrice();
                mallOrderPriceFinder.setLhotelGroupCode(mob.getLhotelGroupCode() );
                mallOrderPriceFinder.setOrderCode( mob.getOrderCode());
                QueryResult<MallOrderPrice> mallOrderPriceQueryResult = commonService.findAllEQ( MallOrderPrice.class,mallOrderPriceFinder );
                if( null!=mallOrderPriceQueryResult && mallOrderPriceQueryResult.getResultlist().size()>0 ){
                    for (MallOrderPrice mop : mallOrderPriceQueryResult.getResultlist() ) {
                        MallGoodBase mallGoodBaseFinder = new MallGoodBase();
                        mallGoodBaseFinder.setLhotelGroupCode(mop.getLhotelGroupCode() );
                        mallGoodBaseFinder.setGoodCode( mop.getGoodCode());
                        MallGoodBase mallGoodBase = commonService.findOneEQ( MallGoodBase.class, mallGoodBaseFinder);

                        MallOrderPriceMapper mopm = new MallOrderPriceMapper();
                        mopm.setGoodCode(mallGoodBase.getGoodCode() );
                        mopm.setName( mallGoodBase.getName());
                        mopm.setBizDate( DateUtil.format(mob.getCreateDatetime(), DateUtil.webFormat));
                        mopm.setCount( mop.getCount().toString());
                        mopm.setExpressMan( mob.getExpressMan() );
                        mopm.setExpressMobile( mob.getExpressMobile());
                        mopm.setExpressAddress( mob.getExpressAddress());
                        mopm.setExpressRemark( mob.getExpressRemark());
                        mopm.setOrderNo(mob.getOrderCode());
                        mopm.setPriceCode( mop.getPriceCode());
                        mopm.setPoint(mop.getPoint() );
                        mopm.setMoney( mop.getMoney());
                        mallOrderPriceMappers.add(mopm );

                    }
                }
            }
        }
        //eryResult<MallOrderPriceMapper> mallOrderPriceMapperQueryResult = new QueryResult<MallOrderPriceMapper>();
        //mallOrderPriceMapperQueryResult.setResultlist(mallOrderPriceMappers );

        List<String> headers = new ArrayList<String>(Arrays.asList("日期","订号","支付类型","积分","金额", "订单产品名称","数量","收货人", "手机号","收货地址","快递公司","运单号码"));
        List<List<Object>> rows = new ArrayList<>();

        for(MallOrderPriceMapper mopm  :mallOrderPriceMappers ){
            List<Object> row = new ArrayList<>();
            row.add( mopm.getBizDate());
            row.add(mopm.getOrderNo());
            row.add(mopm.getPriceCode());
            row.add(mopm.getPoint());
            row.add(mopm.getMoney());
            row.add(mopm.getOrderNo());
            row.add(mopm.getOrderNo());
            row.add(mopm.getName());
            row.add(mopm.getCount());
            row.add(mopm.getExpressMan());
            row.add(mopm.getExpressMobile());
            row.add(mopm.getExpressAddress());
            row.add("");
            row.add("");
            rows.add(row);
        }
        //List<Object> row = new ArrayList<>();


        makeTempExcel(headers, rows);
        return OUTPUT;
    }

    @Override
    public String getMoonCakeSaleSummary(String startDate) throws Exception {

        QueryResult<MoonCakeSaleSummaryMapper>  moonCakeSaleSummaryMapperQueryResult =reportService.getMoonCakeSaleSummary(null);
        List<String> headers = new ArrayList<String>(Arrays.asList("截至日期","部门","购买数量"));
        List<List<Object>> rows = new ArrayList<>();

        for(MoonCakeSaleSummaryMapper mcssm  :moonCakeSaleSummaryMapperQueryResult.getResultlist() ){
            List<Object> row = new ArrayList<>();
            row.add( mcssm.getBizDate());
            row.add(mcssm.getName());
            row.add(mcssm.getTotalCount());
            rows.add(row);
        }
        makeTempExcel(headers, rows);
        return OUTPUT;
    }

    @Override
    public String getMoonCakeSaleDetail(String startDate) throws Exception {
        QueryResult<MoonCakeSaleDetailMapper> moonCakeSaleDetailMapperQueryResult  =reportService.getMoonCakeSaleDetail(null);
        List<String> headers = new ArrayList<String>(Arrays.asList("日期","订单编号","分销号","所属区域,部门","门店,部门","分销员姓名","月饼类型","月饼券购买数量","单价","合计"));
        List<List<Object>> rows = new ArrayList<>();

        for(MoonCakeSaleDetailMapper mcsdm  :moonCakeSaleDetailMapperQueryResult.getResultlist() ){
            List<Object> row = new ArrayList<>();
            row.add( mcsdm.getBuyDate());
            row.add( mcsdm.getOrderCode());
            row.add( mcsdm.getSalesmanNo());
            row.add( mcsdm.getPpname());
            row.add( mcsdm.getName());
            row.add( mcsdm.getSalesmanName());
            row.add( mcsdm.getProductName());
            row.add( mcsdm.getTotalCount());
            row.add( mcsdm.getPrice());
            row.add( mcsdm.getPrices());
            rows.add(row);
        }
        makeTempExcel(headers, rows);
        return OUTPUT;
    }

    @Override
    public String getMoonCakeExpress(String startDate) throws Exception {
        QueryResult<MoonCakeExpressMapper>   moonCakeExpressMapperQueryResult =reportService.getMoonCakeExpress(null);
        List<String> headers = new ArrayList<String>(Arrays.asList("发货订单","月饼类型","数量","收货人","手机","收货地址","发货时间","快递单号"));
        List<List<Object>> rows = new ArrayList<>();

        for(MoonCakeExpressMapper mcem  :moonCakeExpressMapperQueryResult.getResultlist() ){
            List<Object> row = new ArrayList<>();
            row.add( mcem.getExpressNo() );
            row.add( mcem.getGoodName() );
            row.add( mcem.getGoodAmount() );
            row.add( mcem.getReceiverName());
            row.add( mcem.getMobile() );
            row.add( mcem.getAddress() );
            row.add( "" );
            row.add( mcem.getThirdExpressNo()) ;
            rows.add(row);
        }
        makeTempExcel(headers, rows);
        return OUTPUT;
    }

    private void makeStartEnd( Date date){

        if (DateUtil.isBeforeThisMonth(date)) {
            end = DateUtil.getLastDayOfMonth(date);
            start = DateUtil.getFirstDayOfMonth(date);

        } else {
            end = DateUtil.addDays(new Date(), -1);
            start = DateUtil.getFirstDayOfMonth(end);
        }
    }


//    private Map orgnizationMap(String lhotelGroupCode) throws Exception {
//        BaseL2rParamDto param = new BaseL2rParamDto();
//        param.setLhotelGroupCode(lhotelGroupCode);
//        return sidewipeAPIService.mapOrganizationName(param);
//    }
    private void makeTempExcel(List<String> headers, List<List<Object>> rows) throws Exception {
        try (InputStream is = new FileInputStream(TEMPLATE)) {
            try (OutputStream os = new FileOutputStream(OUTPUT)) {
                Transformer transformer = TransformerFactory.createTransformer(is, os);
                InputStream configInputStream = new FileInputStream(DYNAMIC_COLUMNS_DEMO_XML_CONFIG);
                AreaBuilder areaBuilder = new XmlAreaBuilder(configInputStream, transformer);
                List<Area> xlsAreaList = areaBuilder.build();
                Area xlsArea = xlsAreaList.get(0);
                // creating context
                Context context = PoiTransformer.createInitialContext();
                context.putVar("headers", headers);
                context.putVar("rows", rows);
                // applying transformation
                //logger.info("Applying area " + xlsArea.getAreaRef() + " at cell " + new CellRef("Result!A1"));
                xlsArea.applyAt(new CellRef("Result!A1"), context);
                // saving the results to file
                transformer.write();
                // logger.info("Complete");
            }
        }

    }

}
